<?php
namespace Home\Controller\SuperUser;
use Think\Controller;

/**
 * 7.12
 * 交易管理
 * 用户的充值，购买消费纪录
 * 商家的收益
 * 平台的交易额，充值额
 * 0/1/2
 * 加入图表，显示变化趋势？
 * 交易记录
 */
class TradeController extends Controller {

    function _initialize(){
        // \Think\Hook::add('login','Home\\Behaviors\\testBehavior');
    }

    public function index(){
    	// layout(true);
    	// $this->display();
        // $hello = "hello";
        // \Think\Hook::listen('login',$hello);
        $trade = M('Trade');
        $result = $trade->select();
        $this->assign('list',$result);
        $this->display("SuperUser/trade/index");
    }

    public function platform(){

    }

    /**
     * 7.14
     * 交易
     */
    public function trade($id,$amount,$type,$orderid){
        $trade = M('Trade');
        $trade->id = $id;
        $trade->amount = $amount;
        $trade->type = $type;
        $trade->order_id = $orderid;
        date_default_timezone_set("Asia/Shanghai");
        $trade->datetime = date('y-m-d h:i:s',time());
        $trade->add();
    }   

    /**
     * 7.13
     * 充值，消费等交易
     * @return [type] [description]
     */
    public function user($id,$amount){
        // $trade = M('Trade');
        $user = M('User');
        $user->startTrans();
        $oldUser = $user->where(array('user_id'=>$id))->find();
        $user->remain = $oldUser->remain + $amount;
        $result = $user->where(array('user_id'=>$id))->save();
        if($result){
            $user->commit();
        }else{
            $user->rollback();
        }
    }

    public function shop($id,$amount){
        $shop = M('Shop');
        $shop->startTrans();
        $oldShop = $shop->where(array('shop_id'=>$id))->find();
        $shop->remain = $oldShop->remain + $amount;
        $result = $shop->where(array('shop_id'=>$id))->save();
        if($result){
            $shop->commit();
        }else{
            $shop->rollback();
        }
    }


    /**
     * 日期范围生成查询交易记录
     */
    public function range(){
        $trade = M('Trade');

        if(IS_POST){
            $date = I("post.date");
        }else{
            $result = $trade->select();
            dump($result);
        }
    }

    public function listcharge(){
         
        // DB table to use
        $table = 'viprecharge';
         
        // Table's primary key
        $primaryKey = 'recordid';
         
        // Array of database columns which should be read and sent back to DataTables.
        // The `db` parameter represents the column name in the database, while the `dt`
        // parameter represents the DataTables column identifier. In this case object
        // parameter names
        $columns = array(
            array( 'db' => 'recordid', 'dt' => 'recordid' ),
            array( 'db' => 'user_id',  'dt' => 'user_id' ),
            array( 'db' => 'user_name',   'dt' => 'user_name' ),
            array( 'db' => 'datetime',     'dt' => 'datetime' ),
            array( 'db' => 'number',     'dt' => 'number' ),
            array( 'db' => 'status',     'dt' => 'status' )
        );
         
        // SQL server connection information
        $sql_details = array(
            'user' => C('DB_USER'),
            'pass' => C('DB_PWD'),
            'db'   => C('DB_NAME'),
            'host' => C('DB_HOST')
        );

        if(I('get.startdate')&&I('get.enddate'))
            $where = "datetime between '".I('get.startdate')."' AND '".I('get.enddate')."'";
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP
         * server-side, there is no need to edit below this line.
         */
        Vendor('SSP');
        echo json_encode(
            \SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns,null,$where)
        );
    }

    public function recharge(){
        $this->display("SuperUser/trade/recharge");
    }

    public function cash(){
        $this->display("SuperUser/trade/cash");
    }

    public function listcash(){
        $table = 'withdraw';
         
        // Table's primary key
        $primaryKey = 'idwithdraw';
         
        // Array of database columns which should be read and sent back to DataTables.
        // The `db` parameter represents the column name in the database, while the `dt`
        // parameter represents the DataTables column identifier. In this case object
        // parameter names
        $columns = array(
            array( 'db' => 'idwithdraw', 'dt' => 'recordid' ),
            array( 'db' => 'shop_id',  'dt' => 'shop_id' ),
            array( 'db' => 'shop_name',   'dt' => 'shop_name' ),
            array( 'db' => 'datetime',     'dt' => 'datetime' ),
            array( 'db' => 'number',     'dt' => 'number' ),
            array( 'db' => 'type',     'dt' => 'type' ),
            array( 'db' => 'status',     'dt' => 'status' )
        );
         
        // SQL server connection information
        $sql_details = array(
            'user' => C('DB_USER'),
            'pass' => C('DB_PWD'),
            'db'   => C('DB_NAME'),
            'host' => C('DB_HOST')
        );

        if(I('get.startdate')&&I('get.enddate'))
            $where = "datetime between '".I('get.startdate')."' AND '".I('get.enddate')."'";
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP
         * server-side, there is no need to edit below this line.
         */
        Vendor('SSP');
        echo json_encode(
            \SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns,null,$where)
        );
    }

    /**
     * 统计日期区间的交易数
     * @return [type] [description]
     */
    public function lastcashes(){
      $Order = M('Order');
      $start=I('post.start');
      $end = I('post.end');

      if($start!=''&&$end!=''){
          $sql = "DROP PROCEDURE IF EXISTS make_intervals;
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;";

        $Order->execute($sql);
        $sql = "call make_intervals('".$start."','".$end."',1,'MONTH')";
        $Order->execute($sql);
        $result = $Order->query("select DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') as Times,COUNT(idwithdraw) as number from `withdraw` RIGHT JOIN `time_intervals`
 ON DATE_FORMAT(datetime,'%y-%m')= DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') where interval_start between '"
          .$start."' and '".$end."'  group by  DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m')");
        $new_result = array();
        foreach ($result as $key => $value) {
           $new_result[] = array_values($value);
        }
        echo json_encode($new_result);
      }
    }    

    /**
     * 统计日期区间的交易数
     * @return [type] [description]
     */
    public function lastrecharge(){
      $Order = M('Order');
      $start=I('post.start');
      $end = I('post.end');

      if($start!=''&&$end!=''){
          $sql = "DROP PROCEDURE IF EXISTS make_intervals;
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;";

        $Order->execute($sql);
        $sql = "call make_intervals('".$start."','".$end."',1,'MONTH')";
        $Order->execute($sql);
        $result = $Order->query("select DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') as Times,COUNT(recordid) as number from `viprecharge` RIGHT JOIN `time_intervals`
 ON DATE_FORMAT(datetime,'%y-%m')= DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') where interval_start between '"
          .$start."' and '".$end."'  group by  DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m')");
        $new_result = array();
        foreach ($result as $key => $value) {
           $new_result[] = array_values($value);
        }
        echo json_encode($new_result);
      }
    }

    public function tagdone($id=null,$status=1){
      if($id){
        $cg = M('withdraw');
        if($status<3)
        $cg->status = $status;
        if($cg->where(array('id'=>$id))->save()){

        }else{

        }
      }
    }

   /**
     * 数组转xls格式的excel文件
     * @param  array  $data      需要生成excel文件的数组
     * @param  string $filename  生成的excel文件名
     *      示例数据：
            $data = array(
                array(NULL, 2010, 2011, 2012),
                array('Q1',   12,   15,   21),
                array('Q2',   56,   73,   86),
                array('Q3',   52,   61,   69),
                array('Q4',   30,   32,    0),
               );
     */
    function create_xls($data,$filename='纠纷订单.xls'){
        ini_set('max_execution_time', '0');
        Vendor('PHPExcel');
        $filename=str_replace('.xls', '', $filename).'.xls';
        $phpexcel = new \PHPExcel();
        $phpexcel->getProperties()
            ->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
        $phpexcel->getActiveSheet()->fromArray($data);
        $phpexcel->getActiveSheet()->setTitle('Sheet1');
        $phpexcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=$filename");
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        $objwriter = \PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
        $objwriter->save('php://output');
        exit;
    }

    public function gencashxls(){
       $table = 'withdraw';
         
        // Table's primary key
        $primaryKey = 'idwithdraw';
         
        // Array of database columns which should be read and sent back to DataTables.
        // The `db` parameter represents the column name in the database, while the `dt`
        // parameter represents the DataTables column identifier. In this case object
        // parameter names
        $columns = array(
            array( 'db' => 'idwithdraw', 'dt' => 'recordid' ),
            array( 'db' => 'shop_id',  'dt' => 'shop_id' ),
            array( 'db' => 'shop_name',   'dt' => 'shop_name' ),
            array( 'db' => 'datetime',     'dt' => 'datetime' ),
            array( 'db' => 'number',     'dt' => 'number' ),
            array( 'db' => 'type',     'dt' => 'type' ),
            array( 'db' => 'status',     'dt' => 'status' )
        );
         
        // SQL server connection information
        $sql_details = array(
            'user' => C('DB_USER'),
            'pass' => C('DB_PWD'),
            'db'   => C('DB_NAME'),
            'host' => C('DB_HOST')
        );

        if(I('get.startdate')&&I('get.enddate'))
            $where = "datetime between '".I('get.startdate')."' AND '".I('get.enddate')."'";
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP
         * server-side, there is no need to edit below this line.
         */
        Vendor('SSP');
        $data =  \SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns,null,$where);
        $this->create_xls($data['data'],'结算报表.xls');
    }    

    public function genchargexls(){
        // DB table to use
        $table = 'viprecharge';
         
        // Table's primary key
        $primaryKey = 'recordid';
         
        // Array of database columns which should be read and sent back to DataTables.
        // The `db` parameter represents the column name in the database, while the `dt`
        // parameter represents the DataTables column identifier. In this case object
        // parameter names
        $columns = array(
            array( 'db' => 'recordid', 'dt' => 'recordid' ),
            // array( 'db' => 'user_id',  'dt' => 'user_id' ),
            array( 'db' => 'user_name',   'dt' => 'user_name' ),
            array( 'db' => 'datetime',     'dt' => 'datetime' ),
            array( 'db' => 'number',     'dt' => 'number' ),
            // array( 'db' => 'status',     'dt' => 'status' )
        );
         
        // SQL server connection information
        $sql_details = array(
            'user' => C('DB_USER'),
            'pass' => C('DB_PWD'),
            'db'   => C('DB_NAME'),
            'host' => C('DB_HOST')
        );

        if(I('get.startdate')&&I('get.enddate'))
            $where = "datetime between '".I('get.startdate')."' AND '".I('get.enddate')."'";
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP
         * server-side, there is no need to edit below this line.
         */
        Vendor('SSP');
        $title = array(array('编号','账号名','充值日期','数目'));
        $data = \SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns,null,$where);
        // dump(array_merge($title,$data['data']));
        $this->create_xls(array_merge($title,$data['data']),'会员充值报表.xls');

    }



}